<?php
namespace app\api\controller\audience;
use app\api\controller\common\ApiBase;
use Think\Db;
use think\Controller; // dengh quxiao
class Statisticspub extends ApiBase{
    public function _initialize()
    {
        parent::_initialize();
        $this->admin = session('admin');
    }

    public function getTabName($exhibition_id){
        $result=db('exhibition')->where(['id'=>$exhibition_id])->find();
        $tabName='';
        if($result){
            $tabName=$result['create_person_table'];
        }else{
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        return $tabName;
    }

    /**
     * 公海列表  0527
     * @return \think\response\Json|void
     *  // zh_20200528fmr
     */

    public function index(){
        $page = $this->request->param('page');
        $page = max(1,intval($page));
        $pagesize =$this->request->param('pageSize')==""?20:$this->request->param('pageSize');
        $exhibition_id =$this->request->param('exhibition_id');
        $status=$this->request->param('status')==''?1:$this->request->param('status');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $tabName='crm_'.$tabName;
        //back字段 0代表正常 1代表退回数据
        //status前端传入1代表 全部 2代表 未联系 3代表 已联系
        $param=" and is_beforehand=0 ";
        if($status == 1){
            $param.=" and (back=0 or back=1)";
        }else if($status == 2){
            $param.=" and back=0";
        }else if($status == 3){
            $param.=" and back=1";
        }
        $keyword =input('keyword');
        if (!empty($keyword)) {
            $param.=" and company_name like '%$keyword%' ";
        }
        $sql2="select id,create_time,mobile_person,person_id,data_from,company_name,area_city,info_level,name_cn,company_address,link,contact,category_id,second_category_id,back";
        $sql2.=" from ".$tabName." where 1=1 $param";
        $sql2.=" limit ".($page-1)*$pagesize.",$pagesize";
        $list=Db::query($sql2);
        $sql21=" select count(id) as count1 ";
        $sql21.=" from ".$tabName." where 1=1 $param ";
        $total1=Db::query($sql21);
        $total=$total1[0]['count1']; //st
        return json(['code'=>1,'msg'=>'success','data'=>['list'=>$list,'count'=>$total]]);
    }

    /**
     * 数据提取到私海
     * 公海表
     */
    public function give(){
        $exhibition_id = $this->request->param('exhibition_id');//业务员id
        $public_id = $this->request->param('public_id');//公海id
        $data['member_id'] = $this->admin['id'];//提取人id
        $data['update_time'] = time();
        $data['back'] = 2;
        #数据验证
        if(empty($public_id)){
            return json(['code'=>0,'msg'=>'公海数据有误']);
        }
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'展会数据有误']);
        }
        if(empty($data['member_id'])){
            return json(['code'=>0,'msg'=>'业务员有误']);
        }
        $tabName=$this->getTabName($exhibition_id);
        //先查询记录是否存在
        $where['id'] = array('in', $public_id);
        $where['member_id'] = array('neq',0);
        $res = db($tabName)->where($where)->select();
        if($res){
            return json(['code'=>0,'msg'=>'数据已被分配']);
        }
        $errorArr=array();
        $public_id = explode(',',$public_id);
        $is = true;
        foreach ($public_id as $v){
            $result=db($tabName)->where(['id'=>$v])->update($data);
            if(!$result){
                $is=false;
                array_push($errorArr,$v);
            }
        }
        if($is){
            return json(['code'=>1,'msg'=>'操作成功']);
        }else{
            $errIds= implode('，',$errorArr);
            return json(['code'=>0,'msg'=>$errIds.'展商未操作成功']);
        }
    }

    //数据赛选
    public function screen()
    {
        //基础公司表
        //个人信息 中文姓名 职务 部门 手机 数据来源 所属洲 国家 省份 城市  公司中文名 地址 网址 固定电话 一级分类 二级分类
        //$keyword = $this->request->param('keyword');
        // var_dump(111);die;
        $page = $this->request->param('page');
        $where = array();
        $page = max(1, intval($page));
        $pagesize = $this->request->param('pagesize');
        if(empty($pagesize)){
            $pagesize=200;
        }
        $where['is_delete'] = 0; //回头问下
        $exhibition_id = $this->request->param('exhibition_id');//展会id
        if(empty($exhibition_id)){
            return json(['code' => 0, 'msg' => '缺少关键主键展会id']);
        }
        // 查询 st 精确查询还是模糊 查询。。。。擦  都是必要的字段留着 其他的没卵用
        $name_cn = $this->request->param('name_cn'); //中文姓名
        $positions_cn = $this->request->param('positions_cn'); //职务
        $department_cn = $this->request->param('department_cn'); //职务
        $mobile = $this->request->param('mobile'); //手机
        $data_from = $this->request->param('data_from'); //数据来源
        $country = $this->request->param('country');//国家
        $province = $this->request->param('province');  //省份
        $area_city = $this->request->param('area_city');  //地级市
        $company_name = $this->request->param('company_name'); //公司中文名
        $tel = $this->request->param('tel');   //固定电话
        $link = $this->request->param('link'); //网址
        $category_id = $this->request->param('category_id');  //一级 分类
        $second_category_id = $this->request->param('second_category_id');     //二级 分类
        $parameter="is_delete=0";
        if(!empty($name_cn)){
            $where['name_cn'] = array('like', '%' . $name_cn . '%');
            $parameter.=" and name_cn like '%$name_cn%' ";
        }

        if(!empty($positions_cn)){
            $where['positions_cn'] = array('like', '%' . $positions_cn . '%');
            $parameter.=" and positions_cn like '%$positions_cn%' ";
        }

        if(!empty($department_cn)){
            $where['positions_cn'] = array('like', '%' . $department_cn . '%');
            $parameter.=" and department_cn like '%$department_cn%' ";
        }

        if(!empty($mobile)){
            $where['mobile'] = $mobile;
            $parameter.=" and mobile=$mobile";
        }

        if(!empty($data_from)){
            $where['data_from'] = array('like', '%' . $data_from . '%');
            $parameter.=" and data_from like '%$data_from%' ";
        }

        if(!empty($country)){
            $where['country'] = array('like', '%' . $country . '%');
            $parameter.=" and country like '%$country%' ";
        }

        if(!empty($province)){
            $where['province'] = array('like', '%' . $province . '%');
            $parameter.=" and province like '%$province%' ";
        }

        if(!empty($area_city)){
            $where['area_city'] = array('like', '%' . $area_city . '%');
            $parameter.=" and area_city like '%$area_city%' ";
        }

        if(!empty($company_name)){
            $where['company_name'] = array('like', '%' . $company_name . '%');
            $parameter.=" and company_name like '%$company_name%' ";
        }

        if(!empty($tel)){
            $where['tel'] = $tel;
            $parameter.=" and tel like=$tel";
        }

        if(!empty($link)){
            $where['tel'] = $link;
            $parameter.=" and tel=$link";
        }

        if(!empty($category_id)){
            $where['category_id'] = array('like', '%' . $category_id . '%');
            $parameter.=" and category_id like '%$category_id%' ";
        }

        if(!empty($second_category_id)){
            $where['second_category_id'] = array('like', '%' . $second_category_id . '%');
            $parameter.=" and second_category_id like '%$second_category_id%' ";
        }
        //$list =db('company')->field('id,company_name')->where($where)->order('id desc')->limit(($page-1)*$pagesize.','.$pagesize)->select();
        // $total=db('company')->where($where)->count();
        $w['exhibition_id'] = $exhibition_id;
        $newList=array();

        // 查询非公海 企业 0526
        $tabName=$this->getTabName($exhibition_id);
        $tabName='crm_'.$tabName;
        $sql2="select id,company_name from crm_company_user where ".$parameter." and id not in";
        $sql2.=" (select `person_id` from ".$tabName.")";
        if($parameter == 'is_delete=0'){
            $sql2.=" limit ".($page-1)*$pagesize.",$pagesize";
        }
        $list1=Db::query($sql2);

        $sql21="select COUNT(*) as count1 from crm_company_user where ".$parameter." and id not in";
        $sql21.=" (select `person_id` from ".$tabName.")";
        //$sql2.=" limit ".($page-1)*$pagesize.",$pagesize";
        $total=Db::query($sql21);


        $list=$list1;  //st
        $total=$total[0]['count1']; //st
        return json(['code' => 1, 'msg' => 'success', 'data' => ['list' => $list, 'total' => $total]]);
    }


    /**
     * 添加公海记录
     * @return \think\response\Json|void
     */
    public function add(){
        $exhibition_id = $this->request->param('exhibition_id');
        $company_id = $this->request->param('company_id');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'展会数据错误','data'=>'']);
        }
        if(empty($company_id)){
            return json(['code'=>0,'msg'=>'请选择企业','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $company_id = explode(',',$company_id);
        foreach ($company_id as $v){
            #检测重复
            $r = db($tabName)->where(['person_id'=>$v])->find();
            if(!$r){
                $data = db('company_user')->where(['id'=>$v])->find();
                unset($data['id']);
                $data['person_id'] = $v;
                $data['create_time'] = time();
                db($tabName)->insert($data);
            }
        }
        return json(['code'=>1,'msg'=>'操作成功','data'=>'']);
    }


    /**
     * 数据检索
     * @return \think\response\Json|void
     */

    public function UserList()
    {
        $page = $this->request->param('page');
        $compname = $this->request->param('compname');
        $exhibition_id = $this->request->param('exhibition_id');
        $page = max(1, intval($page));
        $pagesize = 10;
        $where['is_delete'] = array('neq',1);
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键主键展会id']);
        }
        if (!empty($compname)) {
            $where['company_name|name_cn'] = array('like', '%' . $compname . '%');
        }else{
            return json(['code'=>0,'msg'=>'请输入名称']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $list = Db::name('company_user')
            ->alias('u')
            ->field('u.id,u.company_name,u.name_cn,u.mobile,g.id as gid')
            ->join($tabName.' g','g.person_id=u.id','left')
            ->where($where)
            ->order('id desc')
            ->limit(($page-1)*$pagesize.','.$pagesize)
            ->select();
         $total = Db::name('company_user')
            ->field('id,company_name,name_cn')
            ->where($where)
            ->count();
            
        foreach($list as &$_val){
            
        }
        unset($_val);
        return json(['code' => 1, 'msg' => 'success', 'data' => ['list' => $list, 'total' => $total, 'countpage' => ceil($total / $pagesize)]]);
    }


    public function export()
    {
        $page = $this->request->param('page');
        $ids = $this->request->param('ids');
        $keyword = $this->request->param('keyword');
        $status = $this->request->param('status');
        $page = max(1, intval($page));
        $pageSize = $this->request->param('pagesize')?$this->request->param('pagesize'):10;
        $exhibition_id = $this->request->param('exhibition_id');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键主键展会id']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $tabName='crm_'.$tabName;
        //back字段 0代表正常 1代表退回数据
        //status前端传入1代表 全部 2代表 未联系 3代表 已联系
        $param="";
        if($status == 1){
            $param=" and (back=0 or back=1)";
        }else if($status == 2){
            $param=" and back=0";
        }else if($status == 3){
            $param=" and back=1";
        }
        $keyword =input('keyword');
        if(!empty($dis)){
            $param.=" and id in ($ids)";
        }else{
            if (!empty($keyword)) {
                $param.=" and company_name like '%$keyword%' ";
            }
        }
        
        $sql2="select id,create_time,person_id,data_from,company_name,area_city,info_level,name_cn,company_address,link,contact,category_id,second_category_id,back";
        $sql2.=" from ".$tabName." where 1=1 $param";
        $sql2.=" limit ".($page-1)*$pageSize.",$pageSize";
        $list=Db::query($sql2);
        //if(!empty($ids) || )
        $columns = array(
            array('title' => '姓名', 'field' => 'realname', 'width' => 12),
            array('title' => '英文姓名', 'field' => 'realname', 'width' => 12),
            array('title' => '电子邮件', 'field' => 'realname', 'width' => 12),
            array('title' => '国家', 'field' => 'realname', 'width' => 12),
            array('title' => '公司名称', 'field' => 'realname', 'width' => 12),
            array('title' => '省份', 'field' => 'ordersn', 'width' => 24),
            array('title' => '城市', 'field' => 'ordersn', 'width' => 24),
            array('title' => '详细地址', 'field' => 'ordersn', 'width' => 24),
            array('title' => '公司名称', 'field' => 'realname', 'width' => 12),
            array('title' => '公司名称英文', 'field' => 'mobile', 'width' => 12),
            array('title' => '行业', 'field' => 'goods_title', 'width' => 24),
            array('title' => '部门', 'field' => 'goods_title', 'width' => 24),
            array('title' => '职位', 'field' => 'goods_title', 'width' => 24),

        );
        $title = "企业明细";
        $filename = $title . date('Y-m-d-H-i', time()) . ".xls";
        $daily = $this->doexport($columns, $filename, $list);
        exit;
    }

    public function doexport($datas, $filename, $list)
    {
        $str = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\nxmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\nxmlns=\"http://www.w3.org/TR/REC-html40\">\r\n<head>\r\n<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">\r\n</head>\r\n<body>";
        $str .= "<table border=1>";
        $tht = "<tr>"; //biao tr
        foreach ($datas as $key => $trno) {
            $tht .= "<th>{$trno['title']}</th>";
        }
        $tht .= "</tr>";
        $str .= $tht;
        foreach ($list as $key) {
            $str .= "<tr>";
            $str .= "<td>$key->name_cn</td>";
            $str .= "<td>$key->name_en</td>";
            $str .= "<td>$key->mobile_person</td>";
            $str .= "<td>$key->email</td>";
            $str .= "<td>$key->country</td>";
            $str .= "<td>$key->country</td>";
            $str .= "<td>$key->province</td>";
            $str .= "<td>$key->area_city</td>";
            $str .= "<td>$key->area</td>";
            $str .= "<td>$key->company_name</td>";
            $str .= "<td>$key->company_name_en</td>";
            $str .= "<td>$key->industrys</td>";
            $str .= "<td>$key->department_cn</td>";
            $str .= "<td>$key->positions_cn</td>";
            $str .= "</tr>";
        }

        $str .= "</table></body></html>";
        header("Content-Type: application/vnd.ms-excel; name='excel'");   #类型
        header("Content-type: application/octet-stream");
        header("Content-Disposition: attachment; filename=" . $filename);
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        header("Expires: 0");
        exit($str);
    }

    //获取观众详细信息
    public function getUserDetail(){
        $id=$this->request->param('id');//企业库人员id
        $exhibition_id = $this->request->param('exhibition_id');
        $exhibition=db('exhibition')->where(['is_delete'=>0,'id'=>$exhibition_id])->find();
        if(!$exhibition){
            return json(['code'=>1,'msg'=>'展会信息错误']);
        }
        if(empty($id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        };
        $tabName=$this->getTabName($exhibition_id);
        $result=db($tabName)->where(['id'=>$id])->find();
        return json(['code'=>1,'msg'=>'请求成功','data'=>$result]);
    }


    public function setPersonEdit(){
        $exhibition_id = $this->request->param('exhibition_id');
        $exhibition=db('exhibition')->where(['is_delete'=>0,'id'=>$exhibition_id])->find();
        if(!$exhibition){
            return json(['code'=>1,'msg'=>'展会信息错误']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $id=$this->request->param('id');//观众人员id
        $types=$this->request->param('type');//观众人员id
        $perJson=db($tabName)->where(['id'=>$id])->find();
        if(!$perJson){
            return json(['code'=>1,'msg'=>'观众信息错误']);
        }
        $data=array();
        $name_cn=$this->request->param('name_cn');//姓名
        if(!empty($name_cn)){
            $data['name_cn']=$name_cn;
        }
        $name_en=$this->request->param('name_en');//英文姓名
        if(!empty($name_en)){
            $data['name_en']=$name_en;
        }
        $mobile_person=$this->request->param('mobile_person');//手机
        if(!empty($mobile_person)){
            $data['mobile_person']=$mobile_person;
        }
        $email=$this->request->param('email');//电子邮箱
        if(!empty($email)){
            $data['email']=$email;
        }
        $country=$this->request->param('country');//国家
        if(!empty($country)){
            $data['country']=$country;
        }
        $province=$this->request->param('province');//省份
        if(!empty($province)){
            $data['province']=$province;
        }
        $area_city=$this->request->param('area_city');//城市
        if(!empty($area_city)){
            $data['area_city']=$area_city;
        }
        $area=$this->request->param('area');//详细地址
        if(!empty($area)){
            $data['area']=$area;
        }
        $company_name=$this->request->param('company_name');//公司中文名
        if(!empty($company_name)){
            $data['company_name']=$company_name;
        }
        $company_name_en=$this->request->param('company_name_en');//公司英文名
        if(!empty($company_name_en)){
            $data['company_name_en']=$company_name_en;
        }
        $industrys=$this->request->param('industrys');//行业
        if(!empty($industrys)){
            $data['industrys']=$industrys;
        }
        $department_cn=$this->request->param('department_cn');//部门
        if(!empty($department_cn)){
            $data['department_cn']=$department_cn;
        }
        $positions_cn=$this->request->param('positions_cn');//职位
        if(!empty($positions_cn)){
            $data['positions_cn']=$positions_cn;
        }
        $data['update_time']=time();
        $result=db($tabName)->where(['id'=>$id])->update($data);
        if(!$result){
            return json(['code'=>0,'msg'=>'展会观众表更新失败']);
        }

        if($types == 2){
            $rs=db('company_user')->where(['id'=>$perJson['person_id']])->update($data);
            if(!$rs){
                return json(['code'=>0,'msg'=>'基础库观众表更新失败']);
            }
        }
        return json(['code'=>1,'msg'=>'操作成功']);
    }
}



